<?php
/**
 * mysql.class.php
 *
 * @desc Sync client class which use mysql database, leave getting catPath fundation
 * @version 0.7a
 * @author Andrew lee <andrew(at)dianboom(dot)com>
 * @package client_api
 * @since 16:28 2011/08/19
 * @copyright 2010-2011 dianboom corporation. All rights reserved.
 */

class mysql_client extends SYNC_CLIENT implements SYNC_CLIENT_INT  {

    /**
     * @method getCategories
     *
     * @desc get categories
     * @return json
     */
    public function getCategories() {
        $fieldQuery = $this->genFieldsQuery(__FUNCTION__);

        $sql = "SELECT $fieldQuery
            FROM `".$this->_dbPrefix.$this->_dbMapping[__FUNCTION__]['table']."`";

        $this->display($this->_dbObj->getArray($sql), 1);
    }


    /**
     * @method getParentCategories
     *
     * @desc get parent categories
     * @param integer $parent_id
     * @return json
     */
    public function getParentCategories($parent_id = 0) {
        $fieldQuery = $this->genFieldsQuery(__FUNCTION__);

        $parent_id = (int)$parent_id;

        $sql = "SELECT $fieldQuery
            FROM `".$this->_dbPrefix.$this->_dbMapping[__FUNCTION__]['table']."`
            WHERE `".$this->_dbMapping[__FUNCTION__]['parent_id']."` = $parent_id";

        $this->display($this->_dbObj->getArray($sql), 1);
    }


    /**
     * @method getBrandList
     *
     * @desc get brand list
     * @return json
     */
    public function getBrandList() {
        $fieldQuery = $this->genFieldsQuery(__FUNCTION__);

        $sql = "SELECT $fieldQuery
            FROM `".$this->_dbPrefix.$this->_dbMapping[__FUNCTION__]['table']."`";

        $this->display($this->_dbObj->getArray($sql), 1);
    }


    /**
     * @method get_categories_by_id
     *
     * @desc get categorie by id
     * @param integer $cat_id default 0
     * @param integer $brand_id
     * @return json
     */
    public function get_categories_by_id($cat_id = 0, $brand_id) {
        $this->get_child_list($cat_id, $brand_id, 0);
    }


    /**
     * @method get_child_list
     *
     * @desc get child categories
     * @param integer $tree_id default 0
     * @param integer $brand_id
     * @param integer $deep default 0
     * @return json
     */
    public function get_child_list($tree_id = 0, $brand_id, $deep = 0) {
        $fieldQuery = $this->genFieldsQuery(__FUNCTION__);

        $sql = "SELECT $fieldQuery
            FROM `".$this->_dbPrefix.$this->_dbMapping[__FUNCTION__]['table']."`";

        if (!intval($tree_id))
            $tree_id = 0;

        $sql .= " WHERE (`".$this->_dbMapping[__FUNCTION__]['parent_id']."` = $tree_id";

        if (!intval($deep))
            $deep = 0;

        if ($deep == 0)
            $sql .= " OR `".$this->_dbMapping[__FUNCTION__]['cat_id']."` = $tree_id";

        $sql .= ")";

        // not with brand
        //$sql .= " AND `".$this->_dbMapping[__FUNCTION__]['cat_id']."` IN (SELECT `".$this->_dbMapping['getProductByCategory']['category_id']."` FROM `".$this->_dbPrefix.$this->_dbMapping['getProductByCategory']['table']."` WHERE `".$this->_dbMapping['getProductByCategory']['brand_id']."` = $brand_id)";

        if (NULL !== $this->_dbMapping[__FUNCTION__]['sort_id'])
            $sql .= " ORDER BY `".$this->_dbMapping[__FUNCTION__]['sort_id']."` ASC";

        $rst = $this->_dbObj->getArray($sql);

        $nextDeep = $deep + 1;

        $array = array();

        foreach($rst as $k=>$v) {
            $array[$v['cat_id']] = $v;

            $nextRst = $this->get_child_list($v['cat_id'], $brand_id, $nextDeep);
            if (is_array($nextRst))
                foreach ($nextRst as $kk=>$vv) 
                    $array[$vv['cat_id']] = $vv;
        }

        0 === $deep && $this->display($array, 1);

        return $array;
    }


    /**
     * @method get_category_by_id
     *
     * @desc get categorie by id
     * @param integer $cat_id
     * @return json
     */
    /*public function get_category_by_id($cat_id) {
        if (($cat_id = intval($cat_id)) < 1)
            $this->display(array(), 1);

        $fieldQuery = $this->genFieldsQuery('get_child_list');

        $sql = "SELECT $fieldQuery
            FROM `".$this->_dbPrefix.$this->_dbMapping['get_child_list']['table']."`
            WHERE `".$this->_dbMapping['get_child_list']['cat_id']."` = $cat_id
            LIMIT 1";

        $rst = $this->_dbObj->getArray($sql);

        $this->display(isset($rst[0])? $rst[0]: array(), 1);
    }*/


    /**
     * @method getProductByCategory
     *
     * @desc get Product By Category And Brand
     * @param integer $cat_id
     * @param integer $brand_id
     * @param integer $start
     * @param integer $limit
     * @return json
     */
    public function getProductByCategory($cat_id, $brand_id, $start = 0, $limit = 0) {
        $cat_id = intval($cat_id);

        $cat_id || $this->display('Invalid category id', -1);

        $brand_id = intval($brand_id);

        //$brand_id || $this->display('Invalid brand id', -1);

        $start = intval($start);

        if ($start < 0)
            $start = 0;

        $limit = intval($limit);

        if (!$limit || $limit < 0)
            $limit = $this->_config['limit'];

        $fieldQuery = $this->genFieldsQuery(__FUNCTION__);

        // not with brand_id
        $sql = "SELECT $fieldQuery
            FROM `".$this->_dbPrefix.$this->_dbMapping[__FUNCTION__]['table']."`
            WHERE 
            -- `".$this->_dbMapping[__FUNCTION__]['brand_id']."` != 0 AND
            `".$this->_dbMapping[__FUNCTION__]['category_id']."` = $cat_id
            GROUP BY 
            `".$this->_dbMapping[__FUNCTION__]['product_name']."`,
            `".$this->_dbMapping[__FUNCTION__]['category_id']."`
            LIMIT $start, $limit";

        $rst = $this->_dbObj->getArray($sql);

        // import seo info
        if (
            is_array($rst) &&
            count($rst) &&
            file_exists($filename = INCLUDE_ROOT. 'seo'. DIRECTORY_SEPARATOR. $this->_config['system']. '.php') &&
            is_readable($filename)
        ) foreach ($rst as $k=>$v)
        ($product_id = $v['product_id']) && ($rst[$k] = array_merge($v, (array)require $filename));

        $this->display($rst, 1);
    }


    /**
     * @method getProductGalleris
     *
     * @desc get Product Galleris
     * @param integer $product_id
     * @return json
     */
    public function getProductGalleris($product_id) {
        $product_id = intval($product_id);

        $product_id || $this->display('Invalid product id', -1);

        $fieldQuery = $this->genFieldsQuery(__FUNCTION__);

        $sql = "SELECT $fieldQuery
            FROM `".$this->_dbPrefix.$this->_dbMapping[__FUNCTION__]['table']."`
            WHERE `".$this->_dbMapping[__FUNCTION__]['product_id']."` = $product_id";

        $rst = $this->_dbObj->getArray($sql);

        foreach($rst as $k=>$v)
            /**
             * @desc add picture path prefix from configure if picture path prefix is not with (http://|https://)
             */
            FALSE === stripos($v['img_path'], 'http://') &&
            FALSE === strpos($v['img_path'], 'https://') &&
            ($rst[$k]['img_path'] = $this->_config['pathMapping'][$this->hashInit($v['img_path'])]. $v['img_path']);

        $this->display($rst, 1);
    }


    /**
     * @method statistics
     *
     * @desc count products and categories
     * @param mixed $cat_id
     * @return integer
     */
    public function statistics($cat_id) {

        $cat_id = trim($cat_id);
        $cat_id = explode(',', $cat_id);
        $cat_id || $this->display('Invalid cat_id');

        $ids = array();

        foreach ($cat_id as $id) {
            $child_id_list = $this->get_child_id_list($id);

            foreach ($child_id_list as $cil)
                $ids[] = $cil;
        }

        $ids = array_unique($ids);

        0 === count($ids) && $this->display(array('cat_count'=>0, 'product_count'=>0), 1);

        // get all count
        $sql = "SELECT COUNT(1) AS n
            FROM `".$this->_dbPrefix.$this->_dbMapping['get_child_list']['table']."`";

        $rst = $this->_dbObj->getArray($sql);

        $cat_count = $rst[0]['n'];

        // product count
        // not with brand_id

		$sql = "SELECT COUNT(1) AS n
			FROM (SELECT DISTINCT `".$this->_dbMapping['getProductByCategory']['product_name']."`,
            `".$this->_dbMapping['getProductByCategory']['category_id']."` FROM `".$this->_dbPrefix.$this->_dbMapping['getProductByCategory']['table']."` WHERE `".$this->_dbMapping['getProductByCategory']['category_id']."` IN (".implode(',', $ids).")) t";

        $rst = $this->_dbObj->getArray($sql);

        $this->display(array('cat_count'=>$cat_count, 'product_count'=>$rst[0]['n']), 1);
    }


    /**
     * @method getProductAttrs
     *
     * @desc get product attributes
     * @param integer $product_id
     * @return array
     */
    public function getProductAttrs($product_id) {
        file_exists($filename = INCLUDE_ROOT. 'attributes'. DIRECTORY_SEPARATOR. $this->_config['system']. '.php') &&
            is_readable($filename) ||
            $this->display(array(), 1);

        $this->display(require $filename, 1);
    }


    /**
     * @method receiveOrders
     *
     * @desc receive orders from dianboom server
     * @param string $order_info
     * @return array
     */
    public function receiveOrders($order_info) {

        $status = 1;

        if (
            file_exists($filename = INCLUDE_ROOT. 'orders'. DIRECTORY_SEPARATOR. $this->_config['system']. '.php') &&
            is_readable($filename)
        ) {
            //$order_info = json_decode($order_info, TRUE);

            if (FALSE === (require $filename))
                $status = 0;
        }
		if($this->_config['ordersDataFormat']=='xml'){
			header('Content-type:application/xml; charset=utf-8');
			echo "<?xml version=\"1.0\" encoding=\"UTF-8\" ?><res>".$status."</res>";
		}else{
        echo json_encode($status);
		}
        exit;
    }


    /**
     * @method hashInit
     *
     * @desc get the domainMapping key by rule
     * @param string $img_path
     * @return string/integer
     */
    private function hashInit($img_path) {

        $keys = array_keys($this->_config['pathMapping']);

        $key = $keys[0];

        file_exists($filename = INCLUDE_ROOT. 'gallery'. DIRECTORY_SEPARATOR. $this->_config['system']. '.php') &&
            is_readable($filename) && ($key = require $filename);

        if (!in_array($key, $keys))
            $key = $keys[0];

        return $key;
    }


    /**
     * @method get_child_id_list
     *
     * @desc get id of child categories
     * @param integer $tree_id default 0
     * @param integer $deep default 0
     * @return json
     */
    private function get_child_id_list($tree_id = 0, $deep = 0) {

        $sql = "SELECT `". $this->_dbMapping['get_child_list']['cat_id']."`
            FROM `".$this->_dbPrefix.$this->_dbMapping['get_child_list']['table']."`";

        if (!intval($tree_id))
            $tree_id = 0;

        $sql .= " WHERE `".$this->_dbMapping['get_child_list']['parent_id']."` = $tree_id";

        if (!intval($deep))
            $deep = 0;

        $rst = $this->_dbObj->getArray($sql);

        $nextDeep = $deep + 1;

        $array = array($tree_id);

        foreach($rst as $k=>$v) {
            $array[] = $v['cat_id'];
            $nextRst = $this->get_child_id_list($v['cat_id'], $nextDeep);
            if (is_array($nextRst)) {
                foreach ($nextRst as $nr)
                    $array[] = $nr;
            }
        }

        return $array;
    }

}
?>
